#include "tj_form_recipeacount.h"
#include "ui_tj_form_recipeacount.h"
#include <QSettings>
#include <QAxObject>
#include <QAxWidget>
#include "pub_var.h"
#include <QMessageBox>
#include <QSqlError>
#include <QtDebug>
#include <QFileInfo>
#include <QJsonObject>
#include <QJsonArray>
#include <windows.h>
#include <QLibrary>
#include <QJsonDocument>
#include <QRandomGenerator>

Form_recipeAcount::Form_recipeAcount(QWidget *parent) :
    QWidget(parent),
    ui(new Ui::Form_recipeAcount)
{
    ui->setupUi(this);

    initial_finished = false;

    //窗口位置
    QSettings setIni("clin.ini", QSettings::IniFormat);
    QRect rct = setIni.value("windowState/cftj").toRect();
    this->setGeometry(rct);

    //默认日期
    QDate dt;
    ui->deStart->setDate(dt.currentDate());
    ui->deEnd->setDate(dt.currentDate());

    //数据库连接
    qro = new QSqlQuery(pub::db_o);
    qro1 = new QSqlQuery(pub::db_o);
    qro2 = new QSqlQuery(pub::db_o);
    qrm = new QSqlQuery(pub::db_m);

    md_query = new QSqlQueryModel;

    //报表打印机
    bzPrinter = setIni.value("bz/printer").toString();

    //处方所在的药房
    QString sq  = "select dept_name,dept_code from com_department where dept_type = 'P' order by dept_name";
    recipe_depts = pub::fillCombo(ui->cbbRecipeDept, sq, -1);

    //工作人员编码
    qrm->exec("select empl_name,empl_code from com_employee where valid_state = '1'");
    while(qrm->next())
    {
        employees.append(qrm->value(1).toString() + "^" + qrm->value(0).toString() + "^") ;
    }

    dlgDelay = new Pub_pop_delay();

    sq = "select dept_name, dept_code from COM_DEPARTMENT where regdept_flag = '1' and valid_state = '1'";
    qrm->exec(sq);
    qDebug()<<"提取门诊科室列表 ： "<<sq;

    ui->cbb_dept->clear();
    ui->cbb_dept->addItem("全部");
    recipe_dept_code = pub::fillCombo(ui->cbb_dept, sq, 0, qro, false);
    recipe_dept_code = "全部^^" + recipe_dept_code ;

    ui->cbb_doct->addItem("全部");
    recipe_doct_code = "全部^^";

    sq = "create temporary table if not exists tmp_doct_recipe_count "
         "("
         "serial_no int , "//
            //"reg_date date , "//
            "reg_date varchar(20) , "//
            "recipe_count int , "//
            "count_001 int , "//
            "count_002 int "//
            ") ";

    qDebug()<<"临时表："<<sq;
    if(!qrm->exec(sq))
    {
        QMessageBox::information(this,"note","未能成功建立临时表！");
        return;
    }

    initial_finished = true;
}

Form_recipeAcount::~Form_recipeAcount()
{
    delete ui;
}

void Form_recipeAcount::closeEvent(QCloseEvent *event)
{
    Q_UNUSED(event);
    QSettings setIni("clin.ini", QSettings::IniFormat);
    setIni.setValue("windowState/cftj", QVariant(this->geometry()));
    destroyW("cftj");
}

void Form_recipeAcount::on_pbExit_clicked()
{
    close();
}

void Form_recipeAcount::on_deStart_dateChanged(const QDate &date)
{
    //ui->deEnd->setDate(date);
}

void Form_recipeAcount::on_pbGenerate_clicked()
{
    qApp->processEvents();  //刷新显示
    if(ui->deStart->date() > ui->deEnd->date()){
        QMessageBox::warning(this, "注意","起始日期不能大于终止日期！");
        return;
    }
    QString dt1 = ui->deStart->date().toString("yyyy/MM/dd");
    QString dt2 = ui->deEnd->date().toString("yyyy/MM/dd");
    QString sq;

    if(ui->cbbRecipeDept->currentText().isEmpty())
    {
        QMessageBox::warning(this, "注意","处方药房不可以空！");
        return;
    }

    //检索药房代码
    int pos = pub::searchStr(recipe_depts, "^", ui->cbbRecipeDept->currentText()) + 1;
    QString dept_code = pub::getStr(recipe_depts, "^", pos);

    ui->pbGenerate->setEnabled(false);

    dlgDelay->setText("正在提取数据。。。");
    dlgDelay->setModal(true);
    dlgDelay->setNeedClose(false);
    dlgDelay->show();
    qApp->processEvents();

    int yearS = ui->deStart->date().year();
    int yearE = ui->deEnd->date().year();
    int monthS = ui->deStart->date().month();
    int monthE = ui->deEnd->date().month();

    if(ui->rbSffs->isChecked())
    {
        //处方统计
        if(monthS != monthE || yearS != yearE){
            QMessageBox::warning(this, "注意","统计处方不可以跨月份！");
            ui->pbGenerate->setEnabled(true);
            dlgDelay->setNeedClose(true);
            dlgDelay->closeWindow();
            return;
        }

        //int dayCount = dayE - dayS + 1 ;  //统计天数
        QString titleStr = QString::number(yearS) + "年" + QString::number(monthS)+"月";
        titleStr.append("处方收费分类统计表");
        qDebug()<<"title string is "<<titleStr<<"\n"        ;
        //处方数量
        sq = "select to_char(i.oper_date, 'dd') d, count(distinct r.recipe_no),"
             " r.doct_dept,i.pact_code "
             " from fin_opb_invoiceinfo i, pha_sto_recipe r "
             " where i.invoice_no = r.invoice_no and trunc(i.oper_date) >= to_date('"
                + dt1 + "','yyyy/mm/dd') and trunc(i.oper_date) <= to_date('"
                + dt2 + "','yyyy/mm/dd') ";
        sq += " and r.drug_dept_code = '" + dept_code + "' ";
        sq += " group by to_char(i.oper_date, 'dd'), i.pact_code, r.doct_dept order by d";

        qDebug()<<"计算处方数量"<<sq;
        if(!qro1->exec(sq)){
            QMessageBox::warning(this, "注意", qro1->lastError().text());
            qDebug()<< qro1->lastError().text();
            qDebug()<< qro1->lastQuery();
            ui->pbGenerate->setEnabled(true);
            dlgDelay->setNeedClose(true);
            dlgDelay->closeWindow();
            return;
        }

        //金额

        sq = "select d, dpt, pact_code, sum(pay_cost), sum(own_cost) from"
             " (select to_char(i.oper_date, 'dd') d,( i.invoice_no),i.pact_code ,"
             " (i.pay_cost),(i.own_cost),"
             " (select r.doct_dept from pha_sto_recipe r where r.invoice_no = i.invoice_no "
             " and rownum = 1) dpt from fin_opb_invoiceinfo i , pha_sto_recipe r "
             " where i.invoice_no = r.invoice_no and trunc(i.oper_date) >= to_date('";
        sq += dt1 + "','yyyy/mm/dd') and trunc(i.oper_date) <= to_date('"+ dt2 + "','yyyy/mm/dd')";
        sq += " and r.drug_dept_code = '" + dept_code + "'  )";
        sq += " group by d, pact_code,  dpt  order by d";
        qDebug()<<"计算处方金额："<<sq;
        if(!qro2->exec(sq)){
            QMessageBox::warning(this, "注意", qro2->lastError().text());
            qDebug()<< qro2->lastError().text();
            qDebug()<< qro2->lastQuery();
            ui->pbGenerate->setEnabled(true);
            dlgDelay->setNeedClose(true);
            dlgDelay->closeWindow();
            return;
        }
        if(!qro2->next() && !qro1->next()){
            QMessageBox::warning(this, "注意", "指定日期范围无数据！");
            ui->pbGenerate->setEnabled(true);
            dlgDelay->setNeedClose(true);
            dlgDelay->closeWindow();
            return;
        }

        int day,count;//日期，处方
        QString department,mod;//科室，收费方式
        double pay,own;//账户金额，现金金额

        QJsonDocument d;
        QJsonArray jsa_val;    //数值
        QJsonObject jot;  // 临时json
        int row ;

        jot.insert("val", titleStr);
        jot.insert("row", 1);
        jot.insert("col", 1);
        jsa_val << jot;

        //清空表格
        QJsonArray jsa_clr;    //清除区域
        jot = d.object();
        jot.insert("row1",5);
        jot.insert("row2",36);
        jot.insert("col1",1);
        jot.insert("col2",4);
        jsa_clr << jot;

        jot = d.object();
        jot.insert("row1",5);
        jot.insert("row2",36);
        jot.insert("col1",6);
        jot.insert("col2",7);
        jsa_clr << jot;

        jot = d.object();
        jot.insert("row1",5);
        jot.insert("row2",36);
        jot.insert("col1",9);
        jot.insert("col2",11);
        jsa_clr << jot;

        jot = d.object();
        jot.insert("row1",5);
        jot.insert("row2",36);
        jot.insert("col1",13);
        jot.insert("col2",14);
        jsa_clr << jot;

        jot = d.object();

        while(true)
        {

            day = qro1->value(0).toInt();//日期
            count = qro1->value(1).toInt(); //处方数量
            department = qro1->value(2).toString(); //科室
            mod = qro1->value(3).toString(); //收费方式
            row = day + 4 ;//由天数确定行数

            jot.insert("val", QString::number(day));
            jot.insert("row", row);
            jot.insert("col", 1);
            jsa_val << jot;

            if(department == "0026" && mod == "1")
            {
                jot.insert("val", QString::number(count));
                jot.insert("row", row);
                jot.insert("col", 6);
                jsa_val << jot;

            }
            else if(department == "0026" && mod == "2")
            {
                jot.insert("val", QString::number(count));
                jot.insert("row", row);
                jot.insert("col", 2);
                jsa_val << jot;

            }
            else if(department == "0028" && mod == "1")
            {
                jot.insert("val", QString::number(count));
                jot.insert("row", row);
                jot.insert("col", 13);
                jsa_val << jot;

            }else if(department == "0028" && mod == "2")
            {
                jot.insert("val", QString::number(count));
                jot.insert("row", row);
                jot.insert("col", 9);
                jsa_val << jot;

            }else{
                qDebug()<<"数据分项类别错误！ "<<department << " - " << mod;

            }

            if(!qro1->next())
            {
                break;
            }
        }

  //金额
        while(true)
        {
            day = qro2->value(0).toInt();//日期
            department = qro2->value(1).toString(); //科室
            mod = qro2->value(2).toString(); //收费方式
            pay = qro2->value(3).toDouble(); //账户支付
            own = qro2->value(4).toDouble(); //现金支付
            row = day + 4 ;//由天数确定行数

            if(department == "0026" && mod == "1")
            {
                jot.insert("val", QString::asprintf("%.2f",own));
                jot.insert("row", row);
                jot.insert("col", 7);
                jsa_val << jot;
            }
            else if(department == "0026" && mod == "2")
            {
                jot.insert("val", QString::asprintf("%.2f",pay));
                jot.insert("row", row);
                jot.insert("col", 3);
                jsa_val << jot;
                jot.insert("val", QString::asprintf("%.2f",own));
                jot.insert("row", row);
                jot.insert("col", 4);
                jsa_val << jot;
            }
            else if(department == "0028" && mod == "1")
            {
                jot.insert("val", QString::asprintf("%.2f",own));
                jot.insert("row", row);
                jot.insert("col", 14);
                jsa_val << jot;
            }
            else if(department == "0028" && mod == "2")
            {
                jot.insert("val", QString::asprintf("%.2f",pay));
                jot.insert("row", row);
                jot.insert("col", 10);
                jsa_val << jot;
                jot.insert("val", QString::asprintf("%.2f",own));
                jot.insert("row", row);
                jot.insert("col", 11);
                jsa_val << jot;

            }else
            {
                qDebug()<<"数据分项类别错误！ "<<department << " - " << mod;

            }

            if(!qro2->next()){
                break;
            }

        }


        qDebug()<<"数据生成完毕，准备打印输出。。。";

        QJsonObject jo; //打印参数
        jo.insert("excel_model", "/sheets/recipe_count.xls");
        jo.insert("cell_value", jsa_val);
        jo.insert("clear_range", jsa_clr);

        //qDebug()<<"jsa_val  : " <<jsa_val<<"\n";

        //jo.insert("grid_range", jsa_rng);
        jo.insert("process_mode", "p"); //s保存，p打印

        pub::printOutExcel(jo);


        QMessageBox::information(this,"注意","打印输出完毕！");

    }
    else if(ui->rbCfdp->isChecked())
    {
        //生成点评表

        sq = "select count(distinct CLINIC_CODE) from PHA_STO_RECIPE where VALID_STATE = '1' "
             " and trunc(FEE_DATE) >= to_date('" + dt1 + "','yyyy/mm/dd') "
                                                         " and trunc(FEE_DATE) <= to_date('" + dt2 + "','yyyy/mm/dd') ";
        sq += " and drug_dept_code = '" + dept_code + "' ";
        qDebug()<<"检索指定日期处方数量："<<sq;
        qro1->exec(sq);
        qro1->next();
        int recipe_count = qro1->value(0).toInt();
        if( recipe_count < 100)
        {
            QMessageBox::information(this, "错误", "处方数量 " + QString::number(recipe_count) + " 小于100张，不符合点评要求！");
            ui->pbGenerate->setEnabled(true);
            dlgDelay->setNeedClose(true);
            dlgDelay->closeWindow();
            return;
        }
        if( recipe_count > 10000)
        {
            QMessageBox::information(this, "错误", "处方数量 " + QString::number(recipe_count) + " 大于 1 万张，请缩小点评时间范围！");
            ui->pbGenerate->setEnabled(true);
            dlgDelay->setNeedClose(true);
            dlgDelay->closeWindow();
            return;
        }

        sq = "select distinct CLINIC_CODE from PHA_STO_RECIPE where VALID_STATE = '1' "
             " and trunc(FEE_DATE) >= to_date('" + dt1 + "','yyyy/mm/dd') "
                                                         " and trunc(FEE_DATE) <= to_date('" + dt2 + "','yyyy/mm/dd') and drug_dept_code = '" + dept_code + "'";
        qDebug()<<"检索指定日期处方 clinic_code ："<<sq;
        qro1->exec(sq);

        QString clinic_code_list = "'clinic_code'";
        if(recipe_count < 400)
        {
            //数量过少，固定间隔取
            int step = recipe_count / 100 ;
            int count = 100;
            while( count > 0)
            {
                for(int i = 0; i< step; i++)
                {
                    qro1->next();
                }
                clinic_code_list.append(",'" + qro1->value(0).toString() + "'");
                count -- ;
            }
        }
        else
        {
            //随机取
            QStringList code_list;
            while(qro1->next())
            {
                code_list << qro1->value(0).toString();
            }
            QMap<QString,QString> indexes;    //
            int tmp;
            while(indexes.size()< 100)
            {
                tmp = QRandomGenerator::global()->bounded(code_list.size());
                indexes.insert(QString::number(tmp), "val");
            }

            QMap<QString, QString>::iterator itor;
            for(itor = indexes.begin(); itor != indexes.end(); itor++)
            {
                clinic_code_list.append(",'" + code_list[itor.key().toInt()] + "'");
            }
        }

        sq = " select max(reg_date), max((REG_DATE - BIRTHDAY) / 365.25), clinic_code, sum(recipe_qty), " // 0 - 3
                " sum(recipe_cost), max(DOCT_CODE), max(DRUGED_OPER), max(SEND_OPER) " // 4 - 7
                " from PHA_STO_RECIPE where CLINIC_CODE in(" + clinic_code_list + ") ";
        sq += " group by clinic_code order by max(reg_date) ";
        qDebug()<<"提取 pha_sto_recipe 数据 :"<<sq;
        qro1->exec(sq);

        QJsonDocument d;
        QJsonArray jsa_val;    //数值
        QJsonObject jot;  // 临时json

        QMap<QString,QString> diags;    //累计诊断数量

        int row = 3;

        int rowCount = 0;

        qro1->exec(sq);
        while(qro1->next())
        {
            row ++;
            rowCount ++ ;

            //处方日期  年龄  药品品种  通用名  处方金额  处方医师  审核  调配药师  核对发药药师

            jot.insert("val", qro1->value(0).toDate().toString("yyyy-MM-dd"));  //日期
            jot.insert("row", row);
            jot.insert("col", 2);
            jsa_val << jot;

            jot.insert("val", QString::number( qro1->value(1).toDouble(), 'f', 1));  //年龄
            jot.insert("row", row);
            jot.insert("col", 3);
            jsa_val << jot;

            jot.insert("val", qro1->value(3).toString()); //药品品种数量
            jot.insert("row", row);
            jot.insert("col", 5);
            jsa_val << jot;

            jot.insert("val", qro1->value(3).toString()); //通用名药品品种数量
            jot.insert("row", row);
            jot.insert("col", 9);
            jsa_val << jot;

            jot.insert("val", QString::number( qro1->value(4).toDouble(), 'f', 1));  //处方金额
            jot.insert("row", row);
            jot.insert("col", 10);
            jsa_val << jot;

            pos = pub::searchStr(employees, "^", qro1->value(5).toString()) ;
            QString tmp = pub::getStr(employees, "^", pos + 1);

            jot.insert("val", tmp);//医生
            jot.insert("row", row);
            jot.insert("col", 11);
            jsa_val << jot;

            if(qro1->value(6).toString().isEmpty())
            {
                pos = pub::searchStr(employees, "^", qro1->value(7).toString()) ;
            }else{
                pos = pub::searchStr(employees, "^", qro1->value(6).toString()) ;
            }
            tmp = pub::getStr(employees, "^", pos + 1);
            jot.insert("val", tmp);//调配
            jot.insert("row", row);
            jot.insert("col", 12);
            jsa_val << jot;

            pos = pub::searchStr(employees, "^", qro1->value(7).toString()) ;
            tmp = pub::getStr(employees, "^", pos + 1);
            jot.insert("val", tmp);//发药
            jot.insert("row", row);
            jot.insert("col", 13);
            jsa_val << jot;

            //	诊断  抗菌药  注射剂  基药数量

            sq = "SELECT met.clinic_code, met.ITEM_CODE, met.CLINICDIAG_CODE, met.CLINICOTHERDIAG1_CODE, " // 0 - 3
                    " met.CLINICOTHERDIAG2_CODE, PHA.IS_ANTIBACTERIAL, PHA.IS_INJECT, PHA.IS_03" // 4 - 7
                    " from MET_ORD_RECIPEDETAIL met "
                    " LEFT JOIN PHA_COM_BASEINFO pha on PHA.drug_code = MET.item_code "
                    " where met.CLINIC_CODE = '" + qro1->value(2).toString() + "'";
            qDebug()<<"提取 met_ord_recipedetail 中的信息："<<sq;
            qro2->exec(sq);
            diags.clear();
            int is03,isinj,isanti; //累计数量
            is03 = isinj = isanti = 0;
            while(qro2->next())
            {
                if(!qro2->value(2).toString().isEmpty())
                {
                    diags.insert(qro2->value(2).toString(), "val");
                }

                if(!qro2->value(3).toString().isEmpty())
                {
                    diags.insert(qro2->value(3).toString(), "val");
                }

                if(!qro2->value(4).toString().isEmpty())
                {
                    diags.insert(qro2->value(4).toString(), "val");
                }

                if(qro2->value(5).toString() == "1")
                {
                    isanti++;
                }

                if(qro2->value(6).toString() == "1")
                {
                    isinj++;
                }
                if(qro2->value(7).toString() == "1")
                {
                    is03++;
                }
            }


            jot.insert("val", QString::number(diags.size()));  //诊断数量
            jot.insert("row", row);
            jot.insert("col", 4);
            jsa_val << jot;

            if(isanti > 1)
            {
                isanti = 1;
            }
            jot.insert("val", QString::number(isanti)); //是否使用抗菌
            jot.insert("row", row);
            jot.insert("col", 6);
            jsa_val << jot;

            if(isinj > 1)
            {
                isinj = 1;
            }
            jot.insert("val", QString::number(isinj)); //是否使用注射药品
            jot.insert("row", row);
            jot.insert("col", 7);
            jsa_val << jot;

            jot.insert("val", QString::number(is03)); //基药数量
            jot.insert("row", row);
            jot.insert("col", 8);
            jsa_val << jot;

        }

        jot.insert("val", "点评人：" + pub::usr_name);
        jot.insert("row", 2);
        jot.insert("col", 1);
        jsa_val << jot;

        jot.insert("val", "处方日期:" + dt1 + " - " + dt2);   //
        jot.insert("row", 2);
        jot.insert("col", 4);
        jsa_val << jot;

        jot.insert("val", "处方总数：" + QString::number(recipe_count));
        jot.insert("row", 2);
        jot.insert("col", 10);
        jsa_val << jot;

        jot.insert("val", "填报时间：" + QDate::currentDate().toString("yyyy/MM/dd"));
        jot.insert("row", 2);
        jot.insert("col", 12);
        jsa_val << jot;

        QJsonArray jsa_clr;    //清除区域
        jot = d.object();
        jot.insert("row1",4);
        jot.insert("row2",103);
        jot.insert("col1",2);
        jot.insert("col2",13);
        jsa_clr << jot;

        QJsonObject jo; //打印参数
        jo.insert("excel_model", "/sheets/chufangdianping.xls");
        jo.insert("process_mode", "ps");
        jo.insert("cell_value", jsa_val);
        jo.insert("clear_range", jsa_clr);

        pub::printOutExcel(jo);

    }
    else if(ui->rb_count_doct->isChecked())
    {

        if(monthS != monthE || yearS != yearE){
            QMessageBox::warning(this, "注意","统计处方不可以跨月份！");
            ui->pbGenerate->setEnabled(true);
            dlgDelay->setNeedClose(true);
            dlgDelay->closeWindow();
            return;
        }
        QString dt_condition = " trunc(fee_date) >= to_date('" + dt1 + "','yyyy/mm/dd') ";
        dt_condition += " and trunc(fee_date) <= to_date('" + dt2 + "','yyyy/mm/dd') ";

        pos = pub::searchStr(recipe_dept_code, "^", ui->cbb_dept->currentText()) + 1;
        QString doct_dept_code = pub::getStr(recipe_dept_code, "^", pos);

        pos = pub::searchStr(recipe_doct_code, "^", ui->cbb_doct->currentText()) + 1;
        QString doct_code = pub::getStr(recipe_doct_code, "^", pos);

        QString dept_condition = "";
        if(!doct_dept_code.isEmpty())
        {
            dept_condition += " and doct_dept = '" + doct_dept_code + "' ";
        }
        if(!doct_code.isEmpty())
        {
            dept_condition += " and doct_code = '" + doct_code + "' ";
        }

        //处方数量
        sq = "select trunc(fee_date) FEE_DATE_T, count(distinct recipe_no)"
             " from FIN_OPB_FEEDETAIL "
             " where " + dt_condition + dept_condition;
        sq += " and drug_flag = '1' and exec_dpcd = '" + dept_code + "'";
        sq += " group by trunc(fee_date) ";
        sq += " order by fee_date_t ";
        qDebug()<<"提取处方数量："<<sq;
        if(!qro->exec(sq))
        {
            QMessageBox::warning(this, "注意","提取处方数量 sql 失败！");
            ui->pbGenerate->setEnabled(true);
            dlgDelay->setNeedClose(true);
            dlgDelay->closeWindow();
            return;
        }

        //清理旧数据
        qrm->exec("delete from tmp_doct_recipe_count ");

        int sn = 0;
        while(qro->next())
        {
            sn ++ ;

            sq = "insert into tmp_doct_recipe_count"
                 "( serial_no, "     //
                    "reg_date, "     //费用日期
                    "recipe_count "   //处方数量
                    ") values(";
            sq += QString::number(sn) + ",";
            sq += "'" + qro->value(0).toDate().toString("yyyy-MM-dd") + "',";
            sq += qro->value(1).toString() ;
            sq += ")";
            qDebug()<<"插入临时表，处方数量："<<sq;
            if(!qrm->exec(sq))
            {
                QMessageBox::warning(this, "注意","插入临时表处方数量失败！");
                ui->pbGenerate->setEnabled(true);
                dlgDelay->setNeedClose(true);
                dlgDelay->closeWindow();
                return;
            }
        }

        //西药，中成药项数量
        sq = "select trunc(fee_date) FEE_DATE, fee_code, count(fee_code)"
             " from FIN_OPB_FEEDETAIL "
             " where " + dt_condition + dept_condition;
        sq += " and drug_flag = '1' ";
        sq += " group by trunc(fee_date), fee_code ";
        qDebug()<<"提取处方项目数量："<<sq;
        if(!qro->exec(sq))
        {
            QMessageBox::warning(this, "注意","提取处方项目数量 sql 失败！");
            ui->pbGenerate->setEnabled(true);
            dlgDelay->setNeedClose(true);
            dlgDelay->closeWindow();
            return;
        }

        while(qro->next())
        {
            QString fee_code = qro->value(1).toString();

            sq = "update tmp_doct_recipe_count set ";
            sq += " count_" + fee_code + " = " + qro->value(2).toString() ;     //西药项目
            sq += " where ";
            sq += " reg_date = '" + qro->value(0).toDate().toString("yyyy-MM-dd") + "'";


            qDebug()<<"变更临时表，处方项目数量："<<sq;
            if(!qrm->exec(sq))
            {
                QMessageBox::warning(this, "注意","变更临时表处方项目数量失败！");
                ui->pbGenerate->setEnabled(true);
                dlgDelay->setNeedClose(true);
                dlgDelay->closeWindow();
                return;
            }
        }

        //提取数据，准备写入 excel

        sq = "select serial_no, reg_date, recipe_count, count_001, count_002"
             " from tmp_doct_recipe_count order by serial_no ";

        qrm->exec(sq);

        QJsonDocument d;
        QJsonArray jsa_val;    //数值
        QJsonObject jot;  // 临时json
        int row = 4;
        int rowCount = 0;

        while(qrm->next())
        {
            row ++;
            rowCount ++ ;

            jot.insert("val", qrm->value("serial_no").toString());
            jot.insert("row", row);
            jot.insert("col", 1);
            jsa_val << jot;

            jot.insert("val", qrm->value("reg_date").toString());
            jot.insert("row", row);
            jot.insert("col", 2);
            jsa_val << jot;

            jot.insert("val", qrm->value("recipe_count").toString());
            jot.insert("row", row);
            jot.insert("col", 3);
            jsa_val << jot;

            jot.insert("val", qrm->value("count_001").toString());
            jot.insert("row", row);
            jot.insert("col", 4);
            jsa_val << jot;

            jot.insert("val", qrm->value("count_002").toString());
            jot.insert("row", row);
            jot.insert("col", 5);
            jsa_val << jot;

        }

        QString date_range = "时间：";
        date_range += ui->deStart->date().toString("yyyy/MM/dd");
        date_range += " - ";
        date_range += ui->deEnd->date().toString("yyyy/MM/dd");
        jot.insert("val", date_range);
        jot.insert("row", 2);
        jot.insert("col", 1);
        jsa_val << jot;

        QString dept = "诊室：";
        dept += ui->cbb_dept->currentText();
        jot.insert("val", dept);
        jot.insert("row", 2);
        jot.insert("col", 4);
        jsa_val << jot;

        QString doct = "医师：";
        if(ui->cbb_doct->currentText().isEmpty())
        {
            doct += "全部";
        }
        else
        {
            doct += ui->cbb_doct->currentText();
        }


        jot.insert("val", doct);
        jot.insert("row", 2);
        jot.insert("col", 5);
        jsa_val << jot;


        //QJsonArray jsa_rng;    //表格区域
        //jot = d.object();
        //QString rg = QString("A3:I%1").arg(QString::number( rowCount + 4 ));
        //jot.insert("range", rg);
        //jsa_rng << jot;

        QJsonObject jo; //打印参数
        jo.insert("excel_model", "/sheets/recipe_count_doc.xls");
        jo.insert("cell_value", jsa_val);
        //jo.insert("grid_range", jsa_rng);
        jo.insert("process_mode", "s"); //只保存，不打印

        pub::printOutExcel(jo);


    }
    else
    {
        QMessageBox::warning(this, "注意","指定的选项无效！");
    }

    ui->pbGenerate->setEnabled(true);
    dlgDelay->setNeedClose(true);
    dlgDelay->closeWindow();
}

void Form_recipeAcount::on_cbb_dept_currentTextChanged(const QString &arg1)
{
    if(!initial_finished)
    {
        return;
    }

    sq = "select name, lpad(code, 6, '0') from com_privileges where role_dept = '" + arg1 + "'";

    qDebug()<<"提取诊室关联的医生："<<sq;

    ui->cbb_doct->clear();
    ui->cbb_doct->addItem("全部");
    recipe_doct_code = pub::fillCombo(ui->cbb_doct, sq, 0, false);
    recipe_doct_code = "全部^^" + recipe_doct_code ;
}

void Form_recipeAcount::on_pb_count_clicked()
{
    qApp->processEvents();  //刷新显示
    if(ui->deStart->date() > ui->deEnd->date()){
        QMessageBox::warning(this, "注意","起始日期不能大于终止日期！");
        return;
    }
    QString dt1 = ui->deStart->date().toString("yyyy/MM/dd");
    QString dt2 = ui->deEnd->date().toString("yyyy/MM/dd");
    QString sq;

    if(ui->cbbRecipeDept->currentText().isEmpty())
    {
        QMessageBox::warning(this, "注意","处方药房不可以空！");
        return;
    }

    //检索药房代码
    int pos = pub::searchStr(recipe_depts, "^", ui->cbbRecipeDept->currentText()) + 1;
    QString dept_code = pub::getStr(recipe_depts, "^", pos);

    ui->pbGenerate->setEnabled(false);

    dlgDelay->setText("正在提取数据。。。");
    dlgDelay->setModal(true);
    dlgDelay->setNeedClose(false);
    dlgDelay->show();
    qApp->processEvents();

    //int yearS = ui->deStart->date().year();
    //int yearE = ui->deEnd->date().year();
    //int monthS = ui->deStart->date().month();
    //int monthE = ui->deEnd->date().month();

    if(ui->rb_count_doct->isChecked())
    {

        ui->lb_title->setText("医师处方统计");

        QString dt_condition = " trunc(fee_date) >= to_date('" + dt1 + "','yyyy/mm/dd') ";
        dt_condition += " and trunc(fee_date) <= to_date('" + dt2 + "','yyyy/mm/dd') ";

        pos = pub::searchStr(recipe_dept_code, "^", ui->cbb_dept->currentText()) + 1;
        QString doct_dept_code = pub::getStr(recipe_dept_code, "^", pos);

        pos = pub::searchStr(recipe_doct_code, "^", ui->cbb_doct->currentText()) + 1;
        QString doct_code = pub::getStr(recipe_doct_code, "^", pos);

        QString dept_condition = "";
        if(!doct_dept_code.isEmpty())
        {
            dept_condition += " and doct_dept = '" + doct_dept_code + "' ";
        }
        if(!doct_code.isEmpty())
        {
            dept_condition += " and doct_code = '" + doct_code + "' ";
        }

        //处方数量
        sq = "select trunc(fee_date) FEE_DATE_T, count(distinct recipe_no)"
             " from FIN_OPB_FEEDETAIL "
             " where " + dt_condition + dept_condition;
        sq += " and drug_flag = '1' and exec_dpcd = '" + dept_code + "'";
        sq += " group by trunc(fee_date) ";
        sq += " order by fee_date_t ";

        qDebug()<<"提取处方数量："<<sq;
        if(!qro->exec(sq))
        {
            QMessageBox::warning(this, "注意","提取处方数量 sql 失败！");
            ui->pbGenerate->setEnabled(true);
            dlgDelay->setNeedClose(true);
            dlgDelay->closeWindow();
            return;
        }

        //清理旧数据
        qrm->exec("delete from tmp_doct_recipe_count ");

        int sn = 0;
        int count = 0;
        while(qro->next())
        {
            sn ++ ;

            sq = "insert into tmp_doct_recipe_count"
                 "( serial_no, "     //
                    "reg_date, "     //费用日期
                    "recipe_count "   //处方数量
                    ") values(";
            sq += QString::number(sn) + ",";
            sq += "'" + qro->value(0).toDate().toString("yyyy-MM-dd") + "',";
            sq += qro->value(1).toString() ;
            sq += ")";

            count += qro->value(1).toInt();

            qDebug()<<"插入临时表，处方数量："<<sq;
            if(!qrm->exec(sq))
            {
                QMessageBox::warning(this, "注意","插入临时表处方数量失败！");
                ui->pbGenerate->setEnabled(true);
                dlgDelay->setNeedClose(true);
                dlgDelay->closeWindow();
                return;
            }
        }

        sq = "insert into tmp_doct_recipe_count"
             "(serial_no,reg_date, "     //费用日期
                "recipe_count "   //处方数量
                ") values(" +QString::number(sn) + ",'合计'," + QString::number(count) + ")";
        qDebug()<<"insert into tmp_doct_recipe_count"<<sq;
        if(!qrm->exec(sq))
        {
            QMessageBox::warning(this, "注意","插入临时表处方数量失败！");
            ui->pbGenerate->setEnabled(true);
            dlgDelay->setNeedClose(true);
            dlgDelay->closeWindow();
            return;
        }

        //西药，中成药项数量
        sq = "select trunc(fee_date) FEE_DATE, fee_code, count(fee_code)"
             " from FIN_OPB_FEEDETAIL "
             " where " + dt_condition + dept_condition;
        sq += " and drug_flag = '1' ";
        sq += " group by trunc(fee_date), fee_code ";
        qDebug()<<"提取处方项目数量："<<sq;
        if(!qro->exec(sq))
        {
            QMessageBox::warning(this, "注意","提取处方项目数量 sql 失败！");
            ui->pbGenerate->setEnabled(true);
            dlgDelay->setNeedClose(true);
            dlgDelay->closeWindow();
            return;
        }

        int ct1 = 0;
        int ct2 = 0;

        while(qro->next())
        {
            QString fee_code = qro->value(1).toString();

            if(fee_code == "001")
            {
                ct1 += qro->value(2).toInt();
            }
            else
            {
                ct2 += qro->value(2).toInt();
            }
            sq = "update tmp_doct_recipe_count set ";
            sq += " count_" + fee_code + " = " + qro->value(2).toString() ;     //西药项目
            sq += " where ";
            sq += " reg_date = '" + qro->value(0).toDate().toString("yyyy-MM-dd") + "'";


            qDebug()<<"变更临时表，处方项目数量："<<sq;
            if(!qrm->exec(sq))
            {
                QMessageBox::warning(this, "注意","变更临时表处方项目数量失败！");
                ui->pbGenerate->setEnabled(true);
                dlgDelay->setNeedClose(true);
                dlgDelay->closeWindow();
                return;
            }
        }

        sq = "update tmp_doct_recipe_count set ";
        sq += " count_001 = " + QString::number(ct1) ;     //
        sq += " , count_002 = " + QString::number(ct2) ;     //
        sq += " where ";
        sq += " reg_date = '合计'";


        qDebug()<<"变更临时表，处方项目数量："<<sq;
        qrm->exec(sq);

        //提取数据，准备写入 excel  serial_no 序号,

        sq = "select reg_date 挂号日期, recipe_count 处方合计,"
             " count_001 中药项目数量, count_002 西药项目数量"
             " from tmp_doct_recipe_count order by serial_no ";

        md_query->setQuery(sq, pub::db_m);
        ui->tv_query->setModel(md_query);
        pub::setColumnWidth(ui->tv_query, "0.3^0.2^0.2^0.2",ui->tv_query->width());

        /*
        qrm->exec(sq);

        QJsonDocument d;
        QJsonArray jsa_val;    //数值
        QJsonObject jot;  // 临时json
        int row = 4;
        int rowCount = 0;

        while(qrm->next())
        {
            row ++;
            rowCount ++ ;

            jot.insert("val", qrm->value("serial_no").toString());
            jot.insert("row", row);
            jot.insert("col", 1);
            jsa_val << jot;

            jot.insert("val", qrm->value("reg_date").toString());
            jot.insert("row", row);
            jot.insert("col", 2);
            jsa_val << jot;

            jot.insert("val", qrm->value("recipe_count").toString());
            jot.insert("row", row);
            jot.insert("col", 3);
            jsa_val << jot;

            jot.insert("val", qrm->value("count_001").toString());
            jot.insert("row", row);
            jot.insert("col", 4);
            jsa_val << jot;

            jot.insert("val", qrm->value("count_002").toString());
            jot.insert("row", row);
            jot.insert("col", 5);
            jsa_val << jot;

        }

        QString date_range = "时间：";
        date_range += ui->deStart->date().toString("yyyy/MM/dd");
        date_range += " - ";
        date_range += ui->deEnd->date().toString("yyyy/MM/dd");
        jot.insert("val", date_range);
        jot.insert("row", 2);
        jot.insert("col", 1);
        jsa_val << jot;

        QString dept = "诊室：";
        dept += ui->cbb_dept->currentText();
        jot.insert("val", dept);
        jot.insert("row", 2);
        jot.insert("col", 4);
        jsa_val << jot;

        QString doct = "医师：";
        if(ui->cbb_doct->currentText().isEmpty())
        {
            doct += "全部";
        }
        else
        {
            doct += ui->cbb_doct->currentText();
        }


        jot.insert("val", doct);
        jot.insert("row", 2);
        jot.insert("col", 5);
        jsa_val << jot;


        //QJsonArray jsa_rng;    //表格区域
        //jot = d.object();
        //QString rg = QString("A3:I%1").arg(QString::number( rowCount + 4 ));
        //jot.insert("range", rg);
        //jsa_rng << jot;

        QJsonObject jo; //打印参数
        jo.insert("excel_model", "/sheets/recipe_count_doc.xls");
        jo.insert("cell_value", jsa_val);
        //jo.insert("grid_range", jsa_rng);
        jo.insert("process_mode", "s"); //只保存，不打印

        pub::printOutExcel(jo);
*/

    }
    else if(ui->rb_invalid_recipe->isChecked())
    {
        sq = "select oper_date 拦截日期, doct_name 医生, "
             " conflict_type 拦截原因, conflict_describe 处方拦截内容"
             " from ys_bz_e_recipe_check where "
             " oper_date >= '" + dt1 + " 00:00:00' and oper_date <= '" + dt2 + " 23:59:59'";

        qDebug()<<"提取处方校验拦截记录："<<sq;

        md_query->setQuery(sq, pub::db_m);
        ui->tv_query->setModel(md_query);
        pub::setColumnWidth(ui->tv_query, "0.15^0.1^0.2^0.5",ui->tv_query->width());

    }
    else
    {
        QMessageBox::warning(this, "注意","指定的选项不需要提取数据，直接输出即可！");
    }
    ui->pbGenerate->setEnabled(true);
    dlgDelay->setNeedClose(true);
    dlgDelay->closeWindow();
}

void Form_recipeAcount::on_rb_invalid_recipe_clicked()
{
    if(ui->rbSffs->isChecked() || ui->rbCfdp->isChecked())
    {
        ui->pbGenerate->setEnabled(true);
        ui->pb_count->setEnabled(false);
    }
    if(ui->rb_count_doct->isChecked())
    {
        ui->pbGenerate->setEnabled(true);
        ui->pb_count->setEnabled(true);

    }
    if(ui->rb_invalid_recipe->isChecked())
    {
        ui->pbGenerate->setEnabled(false);
        ui->pb_count->setEnabled(true);

    }

}

void Form_recipeAcount::on_rbCfdp_clicked()
{
    on_rb_invalid_recipe_clicked();
}

void Form_recipeAcount::on_rb_count_doct_clicked()
{
    on_rb_invalid_recipe_clicked();
}

void Form_recipeAcount::on_rbSffs_clicked()
{
    on_rb_invalid_recipe_clicked();
}
